﻿using System;
using System.Data;
using System.Data.SqlClient;
namespace DAL
{
    /**
     * Classname : HocVien
     * Version information :1.0
     * Date  :26-09-2006
     * Copyright notice : 
     */

    public class DAHocVienTrung : DbObject
    {
        protected SqlConnection connec;
        //Insert HistoryHocvien
        // load so hoc vien theo Trang thai goi cho Report23
        public int loadDemSoHocVienTheoThaiGoiByReport23(int NguoiDungID, int LevelID, string strIDs)
        {
            int numAffected;
            SqlParameter[] parameters = {   new SqlParameter("@NguoiDungID", SqlDbType.Int, 4),
                                            new SqlParameter("@LevelID", SqlDbType.Int, 4),  
                                            new SqlParameter("@IDs", SqlDbType.VarChar,128)  
                                        };
            parameters[0].Value = NguoiDungID;
            parameters[1].Value = LevelID;
            parameters[2].Value = strIDs;

            // create the parameter
            DataSet dsHocVien = RunProcedure("sp_HocVien_loadDemSoHocVienTheoThaiGoiByReport23", parameters, "Tb_HocVien", connec);
            if (dsHocVien != null && dsHocVien.Tables[0].Rows.Count > 0)
            {
                numAffected = Convert.ToInt32(dsHocVien.Tables[0].Rows[0]["Tong"].ToString());
            }
            else
            {
                numAffected = 0;
            }
            return numAffected;
        }
        // load so hoc vien theo Trang thai goi cho Report23
        public int loadDemSoHocVienTheoThaiGoiByReport23(int NguoiDungID, int LevelID, string strIDs, int NguonID)
        {
            int numAffected;
            SqlParameter[] parameters = {   new SqlParameter("@NguoiDungID", SqlDbType.Int, 4),
                                            new SqlParameter("@LevelID", SqlDbType.Int, 4),  
                                            new SqlParameter("@IDs", SqlDbType.VarChar,128) ,
                                            new SqlParameter("@NguonID", SqlDbType.Int, 4) 
                                        };
            parameters[0].Value = NguoiDungID;
            parameters[1].Value = LevelID;
            parameters[2].Value = strIDs;
            parameters[3].Value = NguonID;

            // create the parameter
            DataSet dsHocVien = RunProcedure("sp_HocVien_loadDemSoHocVienTheoThaiGoiByReport23S", parameters, "Tb_HocVien", connec);
            if (dsHocVien != null && dsHocVien.Tables[0].Rows.Count > 0)
            {
                numAffected = Convert.ToInt32(dsHocVien.Tables[0].Rows[0]["Tong"].ToString());
            }
            else
            {
                numAffected = 0;
            }
            return numAffected;
        }

        public int loadDemSoHocVienTheoThaiGoiByReport23(int NguoiDungID, int LevelID, string strIDs, int NguonID, int TruongID)
        {
            int numAffected;
            SqlParameter[] parameters = {   new SqlParameter("@NguoiDungID", SqlDbType.Int, 4),
                                            new SqlParameter("@LevelID", SqlDbType.Int, 4),  
                                            new SqlParameter("@IDs", SqlDbType.VarChar,128) ,
                                            new SqlParameter("@NguonID", SqlDbType.Int, 4) ,
                                            new SqlParameter("@TruongID", SqlDbType.Int, 4)
                                        };
            parameters[0].Value = NguoiDungID;
            parameters[1].Value = LevelID;
            parameters[2].Value = strIDs;
            parameters[3].Value = NguonID;
            parameters[4].Value = TruongID;

            // create the parameter
            DataSet dsHocVien = RunProcedure("sp_HocVien_loadDemSoHocVienTheoThaiGoiByReport23STruong", parameters, "Tb_HocVien", connec);
            if (dsHocVien != null && dsHocVien.Tables[0].Rows.Count > 0)
            {
                numAffected = Convert.ToInt32(dsHocVien.Tables[0].Rows[0]["Tong"].ToString());
            }
            else
            {
                numAffected = 0;
            }
            return numAffected;
        }
        public DataSet loadPhanHocVienAll(int LevelID)
        {
            SqlParameter[] parameters =  {	 new SqlParameter("@LevelID", SqlDbType.Int,4 )  
                                         
										 };
            // set the 0values
            parameters[0].Value = LevelID;
            // create the parameter
            DataSet dsHocVien = RunProcedure("sp_HocVien_loadPhanHocVienAll", parameters, "Tb_HocVien", connec);
            return dsHocVien;
        }
        public DataSet loadPhanHocVienAllbyNguoidung(int LevelID, int NguoiDungID)
        {
            SqlParameter[] parameters =  {	 new SqlParameter("@LevelID", SqlDbType.Int,4 ),  
                                             new SqlParameter("@NguoiDungID", SqlDbType.Int,4 )
										 };
            // set the 0values
            parameters[0].Value = LevelID;
            parameters[1].Value = NguoiDungID;
            // create the parameter
            DataSet dsHocVien = RunProcedure("sp_HocVien_loadPhanHocVienAllbyNguoidung", parameters, "Tb_HocVien", connec);
            return dsHocVien;
        }
        //Chuyen Hoc Vien
        public int insertHistoryChuyenHocVien(string HocVienIDs, int NguoiBiChuyenID, int NguoiNhanID, int NguoiChuyenID, int LevelID)
        {
            int numAffected;
            // create the parameters
            SqlParameter[] parameters =  {	    new SqlParameter("@HocVienIDs", SqlDbType.VarChar,20000),
                                                new SqlParameter("@NguoiBiChuyenID", SqlDbType.Int,4),
                                                new SqlParameter("@NguoiNhanID", SqlDbType.Int,4),
                                                new SqlParameter("@NguoiChuyenID", SqlDbType.Int,4),
                                                new SqlParameter("@LevelID", SqlDbType.Int,4 )                                            
										 };
            // set the 0values
            parameters[0].Value = HocVienIDs.Trim();
            parameters[1].Value = NguoiBiChuyenID;
            parameters[2].Value = NguoiNhanID;
            parameters[3].Value = NguoiChuyenID;
            parameters[4].Value = LevelID;
            // run the procedure
            RunProcedure("sp_HistoryHocVien_insertHistoryHocVienChuyen", parameters, out numAffected, connec);

            return numAffected;
        }
        // Them ban ghi hien hanh
        public int insertHistoryHocVien(string HocVienIDs, int NguoiNhanID, int NguoiChuyenID, int LevelID)
        {
            int numAffected;
            // create the parameters
            SqlParameter[] parameters =  {	    new SqlParameter("@HocVienIDs", SqlDbType.VarChar,20000),
                                                new SqlParameter("@NguoiNhanID", SqlDbType.Int,4),
                                                new SqlParameter("@NguoiChuyenID", SqlDbType.Int,4),
                                                new SqlParameter("@LevelID", SqlDbType.Int,4)
										 };
            // set the 0values
            parameters[0].Value = HocVienIDs.Trim();
            parameters[1].Value = NguoiNhanID;
            parameters[2].Value = NguoiChuyenID;
            parameters[3].Value = LevelID;
            // run the procedure
            RunProcedure("sp_HistoryHocVien_insertHistoryHocVienMain", parameters, out numAffected, connec);

            return numAffected;
        }
        //Update Hoc vien By Gan Quyen
        public int updateHocVienbyGanQuyen(int HocVienID, int NguoiDungID)
        {
            int numAffected;
            // create the parameters
            SqlParameter[] parameters = { 	    new SqlParameter("@ID", SqlDbType.Int ,4),                                               
                                                new SqlParameter("@NguoiDungID", SqlDbType.Int,4)                                            
										    };
            // set the values
            parameters[0].Value = HocVienID;
            parameters[1].Value = NguoiDungID;

            // run the procedure
            RunProcedure("sp_HocVien_updateHocVienbyGanQuyen", parameters, out numAffected, connec);

            return numAffected;
        }

        // return only the record with the specified ID!!!!!!!!!!!
        public string loadMaHocVienTiepTheo()
        {
            int numAffected;
            // create the parameters
            SqlParameter[] parameters =  {	   new SqlParameter("@MaHocVien", SqlDbType.VarChar, 20)
										 };
            // set the values
            parameters[0].Direction = ParameterDirection.Output;
            // run the procedure
            RunProcedure("sp_HocVien_loadHocVienByMaHocVien", parameters, out numAffected, connec);

            return parameters[0].Value.ToString();
        }
        public DataRow loadReport1()
        {
            using (DataSet HocViens = RunProcedure("sp_HocVien_loadReportOne", "Tb_HocVien", connec))
            {
                return HocViens.Tables[0].Rows[0];
            }
        }

        /// <summary>
        /// Created by LinhDH 2012/09/06
        /// </summary>
        /// <returns></returns>
        public DataRow loadReportOneA(string ip_str_tu_ngay
                                        , string ip_str_tu_thang
                                        , string ip_str_tu_nam
                                        , string ip_str_den_ngay
                                        , string ip_str_den_thang
                                        , string ip_str_den_nam
                                        , int ip_i_id_nguoi_dung)
        {
            SqlParameter[] parameters = {   new SqlParameter("@NguoiDungID", SqlDbType.Int, 4),
                                            new SqlParameter("@TuNgay", SqlDbType.VarChar, 2) ,
                                            new SqlParameter("@TuThang", SqlDbType.VarChar, 2),
                                            new SqlParameter("@TuNam", SqlDbType.VarChar, 4) ,
                                            new SqlParameter("@DenNgay", SqlDbType.VarChar, 2) ,
                                            new SqlParameter("@DenThang", SqlDbType.VarChar, 2),
                                            new SqlParameter("@DenNam", SqlDbType.VarChar, 4)
                                        };
            parameters[0].Value = ip_i_id_nguoi_dung;

            parameters[1].Value = ip_str_tu_ngay;
            parameters[2].Value = ip_str_tu_thang;
            parameters[3].Value = ip_str_tu_nam;

            parameters[4].Value = ip_str_den_ngay;
            parameters[5].Value = ip_str_den_thang;
            parameters[6].Value = ip_str_den_nam;

            using (DataSet HocViens = RunProcedure("sp_HocVien_loadReport1A", parameters, "Tb_HocVien", connec))
            {
                return HocViens.Tables[0].Rows[0];
            }
        }
        public DataRow loadReport2()
        {
            using (DataSet HocViens = RunProcedure("sp_HocVien_loadReport2", "Tb_HocVien", connec))
            {
                return HocViens.Tables[0].Rows[0];
            }
        }

        /// <summary>
        /// Created by LinhDH 2012/09/06
        /// </summary>
        /// <returns></returns>
        public DataRow loadReportTwoA(string ip_str_tu_ngay
                                        , string ip_str_tu_thang
                                        , string ip_str_tu_nam
                                        , string ip_str_den_ngay
                                        , string ip_str_den_thang
                                        , string ip_str_den_nam
                                        , int ip_i_id_nguoi_dung)
        {
            SqlParameter[] parameters = {   new SqlParameter("@NguoiDungID", SqlDbType.Int, 4),
                                            new SqlParameter("@TuNgay", SqlDbType.VarChar, 2) ,
                                            new SqlParameter("@TuThang", SqlDbType.VarChar, 2),
                                            new SqlParameter("@TuNam", SqlDbType.VarChar, 4) ,
                                            new SqlParameter("@DenNgay", SqlDbType.VarChar, 2) ,
                                            new SqlParameter("@DenThang", SqlDbType.VarChar, 2),
                                            new SqlParameter("@DenNam", SqlDbType.VarChar, 4)
                                        };
            parameters[0].Value = ip_i_id_nguoi_dung;

            parameters[1].Value = ip_str_tu_ngay;
            parameters[2].Value = ip_str_tu_thang;
            parameters[3].Value = ip_str_tu_nam;

            parameters[4].Value = ip_str_den_ngay;
            parameters[5].Value = ip_str_den_thang;
            parameters[6].Value = ip_str_den_nam;

            using (DataSet HocViens = RunProcedure("sp_HocVien_loadReport2A", parameters, "Tb_HocVien", connec))
            {
                return HocViens.Tables[0].Rows[0];
            }
        }


        public DAHocVienTrung(SqlConnection Connection)
        {
            connec = Connection;
        }
        // return all the HocViens select Language
        // Danh cho nguoi quan tri
        public int loadDemSoHocVienTheoThaiGoiByLevel(int NguoiDungID, int LevelID, int TrangThaiGoiID)
        {
            int numAffected;
            SqlParameter[] parameters = {   new SqlParameter("@NguoiDungID", SqlDbType.Int, 4),
                                            new SqlParameter("@LevelID", SqlDbType.Int, 4) ,
                                            new SqlParameter("@TrangThaiGoiID", SqlDbType.Int, 4) 
                                        };
            parameters[0].Value = NguoiDungID;
            parameters[1].Value = LevelID;
            parameters[2].Value = TrangThaiGoiID;

            // create the parameter
            DataSet dsHocVien = RunProcedure("sp_HocVien_loadDemSoHocVienTheoThaiGoiByLevel", parameters, "Tb_HocVien", connec);
            if (dsHocVien != null && dsHocVien.Tables[0].Rows.Count > 0)
            {
                numAffected = Convert.ToInt32(dsHocVien.Tables[0].Rows[0]["Tong"].ToString());
            }
            else
            {
                numAffected = 0;
            }
            return numAffected;
        }


        public int loadDemSoHocVienTheoThaiGoiByLevelAndNgay(int NguoiDungID
                                                            , int LevelID
                                                            , int TrangThaiGoiID
                                                            , string ip_str_tu_ngay, string ip_str_tu_thang, string ip_str_tu_nam
                                                            , string ip_str_den_ngay, string ip_str_den_thang, string ip_str_den_nam)
        {
            int numAffected;
            SqlParameter[] parameters = {   new SqlParameter("@NguoiDungID", SqlDbType.Int, 4),
                                            new SqlParameter("@LevelID", SqlDbType.Int, 4) ,
                                            new SqlParameter("@TrangThaiGoiID", SqlDbType.Int, 4),
                                            new SqlParameter("@TuNgay", SqlDbType.VarChar, 4),
                                            new SqlParameter("@TuThang", SqlDbType.VarChar, 4), 
                                            new SqlParameter("@TuNam", SqlDbType.VarChar, 4),
                                            new SqlParameter("@DenNgay", SqlDbType.VarChar, 4),
                                            new SqlParameter("@DenThang", SqlDbType.VarChar, 4), 
                                            new SqlParameter("@DenNam", SqlDbType.VarChar, 4)
                                        };
            parameters[0].Value = NguoiDungID;
            parameters[1].Value = LevelID;
            parameters[2].Value = TrangThaiGoiID;
            parameters[3].Value = ip_str_tu_ngay;
            parameters[4].Value = ip_str_tu_thang;
            parameters[5].Value = ip_str_tu_nam;
            parameters[6].Value = ip_str_den_ngay;
            parameters[7].Value = ip_str_den_thang;
            parameters[8].Value = ip_str_den_nam;

            // create the parameter
            DataSet dsHocVien = RunProcedure("sp_HocVien_loadDemSoHocVienTheoThaiGoiByLevelAndThoiGianGoi", parameters, "Tb_HocVien", connec);
            if (dsHocVien != null && dsHocVien.Tables[0].Rows.Count > 0)
            {
                numAffected = Convert.ToInt32(dsHocVien.Tables[0].Rows[0]["Tong"].ToString());
            }
            else
            {
                numAffected = 0;
            }
            return numAffected;
        }

        /*
         LinDH created 2012.09.27
         */
        public int loadDemSoHocVienTheoThaiGoiByLevelAndDenNgayNhap(int NguoiDungID
                                                                , int LevelID
                                                                , int TrangThaiGoiID
                                                                , int NguonID
                                                                , int TruongID
                                                                , string ip_str_den_ngay
                                                                , int ip_i_loai_convert_date)
        {
            int numAffected;
            SqlParameter[] parameters = {   new SqlParameter("@NguoiDungID", SqlDbType.Int, 4),
                                            new SqlParameter("@LevelID", SqlDbType.Int, 4) ,
                                            new SqlParameter("@TrangThaiGoiID", SqlDbType.Int, 4) ,
                                            new SqlParameter("@NguonID", SqlDbType.Int, 4),
                                                                                       
                                            new SqlParameter("@NhapDenNgay", SqlDbType.VarChar, 11),
                                            new SqlParameter("@TruongID", SqlDbType.Int, 4),
                                            new SqlParameter("@loai_convert_date", SqlDbType.Int, 4)
                                        };
            parameters[0].Value = NguoiDungID;
            parameters[1].Value = LevelID;
            parameters[2].Value = TrangThaiGoiID;
            parameters[3].Value = NguonID;
            parameters[5].Value = TruongID;
            parameters[4].Value = ip_str_den_ngay;
            parameters[6].Value = ip_i_loai_convert_date;

            // create the parameter
            DataSet dsHocVien = RunProcedure("sp_HocVien_loadDemSoHocVienTheoThaiGoiByLevelByNguonAndDenNgayNhap", parameters, "Tb_HocVien", connec);
            if (dsHocVien != null && dsHocVien.Tables[0].Rows.Count > 0)
            {
                numAffected = Convert.ToInt32(dsHocVien.Tables[0].Rows[0]["Tong"].ToString());
            }
            else
            {
                numAffected = 0;
            }
            return numAffected;
        }


        /*
         LinDH created 2012.09.24
         */
        public int loadDemSoHocVienTheoThaiGoiByLevelAndNgayNhap(int NguoiDungID
                                                                , int LevelID
                                                                , int TrangThaiGoiID
                                                                , int NguonID
                                                                , int TruongID
                                                                , string ip_str_tu_ngay, string ip_str_tu_thang, string ip_str_tu_nam
                                                                , string ip_str_den_ngay, string ip_str_den_thang, string ip_str_den_nam)
        {
            int numAffected;
            SqlParameter[] parameters = {   new SqlParameter("@NguoiDungID", SqlDbType.Int, 4),
                                            new SqlParameter("@LevelID", SqlDbType.Int, 4) ,
                                            new SqlParameter("@TrangThaiGoiID", SqlDbType.Int, 4) ,
                                            new SqlParameter("@NguonID", SqlDbType.Int, 4),
                                            
                                            new SqlParameter("@NhapTuNgay", SqlDbType.VarChar, 2) ,
                                            new SqlParameter("@NhapTuThang", SqlDbType.VarChar, 2),
                                            new SqlParameter("@NhapTuNam", SqlDbType.VarChar, 4) ,
                                           
                                            new SqlParameter("@NhapDenNgay", SqlDbType.VarChar, 2) ,
                                            new SqlParameter("@NhapDenThang", SqlDbType.VarChar, 2),
                                            new SqlParameter("@NhapDenNam", SqlDbType.VarChar, 4),
                                            new SqlParameter("@TruongID", SqlDbType.Int, 4)
                                        };
            parameters[0].Value = NguoiDungID;
            parameters[1].Value = LevelID;
            parameters[2].Value = TrangThaiGoiID;
            parameters[3].Value = NguonID;
            parameters[10].Value = TruongID;

            parameters[4].Value = ip_str_tu_ngay;
            parameters[5].Value = ip_str_tu_thang;
            parameters[6].Value = ip_str_tu_nam;

            parameters[7].Value = ip_str_den_ngay;
            parameters[8].Value = ip_str_den_thang;
            parameters[9].Value = ip_str_den_nam;

            // create the parameter
            DataSet dsHocVien = RunProcedure("sp_HocVien_loadDemSoHocVienTheoThaiGoiByLevelByNguonAndThoiGianNhap", parameters, "Tb_HocVien", connec);
            if (dsHocVien != null && dsHocVien.Tables[0].Rows.Count > 0)
            {
                numAffected = Convert.ToInt32(dsHocVien.Tables[0].Rows[0]["Tong"].ToString());
            }
            else
            {
                numAffected = 0;
            }
            return numAffected;
        }
        /*
       LinDH created 2012.09.24
       */
        public int loadDemSoHocVienTheoThaiGoiByLevelAndNgayLichSu(int NguoiDungID
                                                                , int LevelID
                                                                , int TrangThaiGoiID
                                                                , int NguonID
                                                                , int TruongID
                                                                , string ip_str_tu_ngay
                                                                , string ip_str_den_ngay
                                                                , int ip_i_loai_convet_date)
        {
            int numAffected;
            SqlParameter[] parameters = {   new SqlParameter("@NguoiDungID", SqlDbType.Int, 4),
                                            new SqlParameter("@LevelID", SqlDbType.Int, 4) ,
                                            new SqlParameter("@TrangThaiGoiID", SqlDbType.Int, 4) ,
                                            new SqlParameter("@NguonID", SqlDbType.Int, 4),
                                            
                                            new SqlParameter("@NhapTuNgay", SqlDbType.VarChar, 11) ,
                                            new SqlParameter("@NhapDenNgay", SqlDbType.VarChar, 11),
                                            new SqlParameter("@TruongID", SqlDbType.Int, 4),
                                            new SqlParameter("@loai_convert_date", SqlDbType.Int, 4)
                                        };
            parameters[0].Value = NguoiDungID;
            parameters[1].Value = LevelID;
            parameters[2].Value = TrangThaiGoiID;
            parameters[3].Value = NguonID;
            parameters[6].Value = TruongID;
            parameters[7].Value = ip_i_loai_convet_date;

            parameters[4].Value = ip_str_tu_ngay;
            parameters[5].Value = ip_str_den_ngay;

            // create the parameter
            DataSet dsHocVien = RunProcedure("sp_HocVien_loadDemSoHocVienTheoThaiGoiByLevelByNguonAndNgayLichSu", parameters, "Tb_HocVien", connec);
            if (dsHocVien != null && dsHocVien.Tables[0].Rows.Count > 0)
            {
                numAffected = Convert.ToInt32(dsHocVien.Tables[0].Rows[0]["Tong"].ToString());
            }
            else
            {
                numAffected = 0;
            }
            return numAffected;
        }
        // return all the HocViens select Language
        // Danh cho nguoi quan tri
        public int loadDemSoHocVienTheoThaiGoiByLevel(int NguoiDungID, int LevelID
                                                                , int TrangThaiGoiID, int NguonID)
        {
            int numAffected;
            SqlParameter[] parameters = {   new SqlParameter("@NguoiDungID", SqlDbType.Int, 4),
                                            new SqlParameter("@LevelID", SqlDbType.Int, 4) ,
                                            new SqlParameter("@TrangThaiGoiID", SqlDbType.Int, 4) ,
                                            new SqlParameter("@NguonID", SqlDbType.Int, 4)
                                        };
            parameters[0].Value = NguoiDungID;
            parameters[1].Value = LevelID;
            parameters[2].Value = TrangThaiGoiID;
            parameters[3].Value = NguonID;

            // create the parameter
            DataSet dsHocVien = RunProcedure("sp_HocVien_loadDemSoHocVienTheoThaiGoiByLevelByNguon", parameters, "Tb_HocVien", connec);
            if (dsHocVien != null && dsHocVien.Tables[0].Rows.Count > 0)
            {
                numAffected = Convert.ToInt32(dsHocVien.Tables[0].Rows[0]["Tong"].ToString());
            }
            else
            {
                numAffected = 0;
            }
            return numAffected;
        }
        public int loadDemSoHocVienTheoThaiGoiByLevel(int NguoiDungID, int LevelID, int TrangThaiGoiID, int NguonID, int TruongID)
        {
            int numAffected;
            SqlParameter[] parameters = {   new SqlParameter("@NguoiDungID", SqlDbType.Int, 4),
                                            new SqlParameter("@LevelID", SqlDbType.Int, 4) ,
                                            new SqlParameter("@TrangThaiGoiID", SqlDbType.Int, 4) ,
                                            new SqlParameter("@NguonID", SqlDbType.Int, 4) ,
                                            new SqlParameter("@TruongID", SqlDbType.Int, 4) 
                                        };
            parameters[0].Value = NguoiDungID;
            parameters[1].Value = LevelID;
            parameters[2].Value = TrangThaiGoiID;
            parameters[3].Value = NguonID;
            parameters[4].Value = TruongID;

            // create the parameter
            DataSet dsHocVien = RunProcedure("sp_HocVien_loadDemSoHocVienTheoThaiGoiByLevelByNguonTruong", parameters, "Tb_HocVien", connec);
            if (dsHocVien != null && dsHocVien.Tables[0].Rows.Count > 0)
            {
                numAffected = Convert.ToInt32(dsHocVien.Tables[0].Rows[0]["Tong"].ToString());
            }
            else
            {
                numAffected = 0;
            }
            return numAffected;
        }
        // return all the HocViens select Language
        // Danh cho nguoi quan tri
        public int loadDemSoHocVienTheoNguoiDung(int NguoiDungID, int LevelID)
        {
            int numAffected;
            SqlParameter[] parameters = {   new SqlParameter("@NguoiDungID", SqlDbType.Int, 4),
                                            new SqlParameter("@LevelID", SqlDbType.Int, 4) 
                                        };
            parameters[0].Value = NguoiDungID;
            parameters[1].Value = LevelID;

            // create the parameter
            DataSet dsHocVien = RunProcedure("sp_HocVien_loadDemSoHocVienTheoNguoiDung", parameters, "Tb_HocVien", connec);
            if (dsHocVien != null && dsHocVien.Tables[0].Rows.Count > 0)
            {
                numAffected = Convert.ToInt32(dsHocVien.Tables[0].Rows[0]["Tong"].ToString());
            }
            else
            {
                numAffected = 0;
            }
            return numAffected;
        }
        /* LinhDH created 2012.09.27
         * Hàm này để tính report học viên từ trước đến 1 ngày nào đó
         */
        public int load_dem_so_hoc_vien_by_level_truong_nguon_and_den_thoi_gian_nhap(int NguoiDungID
                                                , int LevelID
                                                , int NguonID
                                                , int TruongID
                                                , string ip_str_den_ngay
                                                , int ip_i_loai_convert_date)
        {
            int numAffected;
            SqlParameter[] parameters = {   new SqlParameter("@NguoiDungID", SqlDbType.Int, 4),
                                            new SqlParameter("@LevelID", SqlDbType.Int, 4) ,
                                            new SqlParameter("@NguonID", SqlDbType.Int, 4),
                                           new SqlParameter("@TruongID", SqlDbType.Int, 4),
                                            new SqlParameter("@NhapDenNgay", SqlDbType.VarChar, 11),
                                            new SqlParameter("@v_loai_convert_date", SqlDbType.Int, 4)
                                        };
            parameters[0].Value = NguoiDungID;
            parameters[1].Value = LevelID;
            parameters[2].Value = NguonID;

            parameters[4].Value = ip_str_den_ngay;
            parameters[3].Value = TruongID;
            parameters[5].Value = ip_i_loai_convert_date;

            // create the parameter
            DataSet dsHocVien = RunProcedure("sp_HocVien_loadDemSoHocVienTheoNguoiDungByNguonAndDenThoi_gian_nhap", parameters, "Tb_HocVien", connec);
            if (dsHocVien != null && dsHocVien.Tables[0].Rows.Count > 0)
            {
                numAffected = Convert.ToInt32(dsHocVien.Tables[0].Rows[0]["Tong"].ToString());
            }
            else
            {
                numAffected = 0;
            }
            return numAffected;
        }

        /* LinhDH created 2012.09.24
         */
        public int loadDemSoHocVienTheoNguoiDungAndNgayNhap(int NguoiDungID
                                                , int LevelID
                                                , int NguonID
                                                , int TruongID
                                                , string ip_str_tu_ngay
                                                , string ip_str_tu_thang
                                                , string ip_str_tu_nam
                                                , string ip_str_den_ngay
                                                , string ip_str_den_thang
                                                , string ip_str_den_nam)
        {
            int numAffected;
            SqlParameter[] parameters = {   new SqlParameter("@NguoiDungID", SqlDbType.Int, 4),
                                            new SqlParameter("@LevelID", SqlDbType.Int, 4) ,
                                            new SqlParameter("@NguonID", SqlDbType.Int, 4),
                                            
                                            new SqlParameter("@NhapTuNgay", SqlDbType.VarChar, 2),
                                            new SqlParameter("@NhapTuThang", SqlDbType.VarChar, 2),
                                            new SqlParameter("@NhapTuNam", SqlDbType.VarChar, 4),
                                           
                                            new SqlParameter("@NhapDenNgay", SqlDbType.VarChar, 2) ,
                                            new SqlParameter("@NhapDenThang", SqlDbType.VarChar, 2),
                                            new SqlParameter("@NhapDenNam", SqlDbType.VarChar, 4),
                                            new SqlParameter("@TruongID", SqlDbType.Int, 4)
                                        };
            parameters[0].Value = NguoiDungID;
            parameters[1].Value = LevelID;
            parameters[2].Value = NguonID;

            parameters[3].Value = ip_str_tu_ngay;
            parameters[4].Value = ip_str_tu_thang;
            parameters[5].Value = ip_str_tu_nam;

            parameters[6].Value = ip_str_den_ngay;
            parameters[7].Value = ip_str_den_thang;
            parameters[8].Value = ip_str_den_nam;
            parameters[9].Value = TruongID;

            // create the parameter
            DataSet dsHocVien = RunProcedure("sp_HocVien_loadDemSoHocVienTheoNguoiDungByNguonAndThoiGianNhap", parameters, "Tb_HocVien", connec);
            if (dsHocVien != null && dsHocVien.Tables[0].Rows.Count > 0)
            {
                numAffected = Convert.ToInt32(dsHocVien.Tables[0].Rows[0]["Tong"].ToString());
            }
            else
            {
                numAffected = 0;
            }
            return numAffected;
        }

        /* LinhDH created 2012.09.24
       */
        public int loadDemSoHocVienTheoNguoiDungAndNgayLichSu(int NguoiDungID
                                                , int LevelID
                                                , int NguonID
                                                , int TruongID
                                                , string ip_str_tu_ngay
                                                , string ip_str_den_ngay
                                                , int ip_loai_convert_date)
        {
            int numAffected;
            SqlParameter[] parameters = {   new SqlParameter("@NguoiDungID", SqlDbType.Int, 4),
                                            new SqlParameter("@LevelID", SqlDbType.Int, 4) ,
                                            new SqlParameter("@NguonID", SqlDbType.Int, 4),
                                            
                                            new SqlParameter("@NhapTuNgay", SqlDbType.VarChar, 11),
                                            new SqlParameter("@NhapDenNgay", SqlDbType.VarChar, 11) ,
                                            new SqlParameter("@TruongID", SqlDbType.Int, 4),
                                            new SqlParameter("@loai_convert_date", SqlDbType.Int, 4)
                                        };
            parameters[0].Value = NguoiDungID;
            parameters[1].Value = LevelID;
            parameters[2].Value = NguonID;

            parameters[3].Value = ip_str_tu_ngay;
            parameters[4].Value = ip_str_den_ngay;
            parameters[5].Value = TruongID;
            parameters[6].Value = ip_loai_convert_date;

            // create the parameter
            DataSet dsHocVien = RunProcedure("sp_HocVien_loadDemSoHocVienTheoNguoiDungByNguonAndNgayLichSu", parameters, "Tb_HocVien", connec);
            if (dsHocVien != null && dsHocVien.Tables[0].Rows.Count > 0)
            {
                numAffected = Convert.ToInt32(dsHocVien.Tables[0].Rows[0]["Tong"].ToString());
            }
            else
            {
                numAffected = 0;
            }
            return numAffected;
        }
        // return all the HocViens select Language
        // Danh cho nguoi quan tri
        public int loadDemSoHocVienTheoNguoiDung(int NguoiDungID, int LevelID, int NguonID)
        {
            int numAffected;
            SqlParameter[] parameters = {   new SqlParameter("@NguoiDungID", SqlDbType.Int, 4),
                                            new SqlParameter("@LevelID", SqlDbType.Int, 4) ,
                                            new SqlParameter("@NguonID", SqlDbType.Int, 4) 
                                        };
            parameters[0].Value = NguoiDungID;
            parameters[1].Value = LevelID;
            parameters[2].Value = NguonID;

            // create the parameter
            DataSet dsHocVien = RunProcedure("sp_HocVien_loadDemSoHocVienTheoNguoiDungByNguon", parameters, "Tb_HocVien", connec);
            if (dsHocVien != null && dsHocVien.Tables[0].Rows.Count > 0)
            {
                numAffected = Convert.ToInt32(dsHocVien.Tables[0].Rows[0]["Tong"].ToString());
            }
            else
            {
                numAffected = 0;
            }
            return numAffected;
        }

        public int loadDemSoHocVienTheoNguoiDung(int NguoiDungID, int LevelID, int NguonID, int TruongID)
        {
            int numAffected;
            SqlParameter[] parameters = {   new SqlParameter("@NguoiDungID", SqlDbType.Int, 4),
                                            new SqlParameter("@LevelID", SqlDbType.Int, 4) ,
                                            new SqlParameter("@NguonID", SqlDbType.Int, 4),
                                            new SqlParameter("@TruongID", SqlDbType.Int, 4)
                                        };
            parameters[0].Value = NguoiDungID;
            parameters[1].Value = LevelID;
            parameters[2].Value = NguonID;
            parameters[3].Value = TruongID;

            // create the parameter
            DataSet dsHocVien = RunProcedure("sp_HocVien_loadDemSoHocVienTheoNguoiDungByNguonTruong", parameters, "Tb_HocVien", connec);
            if (dsHocVien != null && dsHocVien.Tables[0].Rows.Count > 0)
            {
                numAffected = Convert.ToInt32(dsHocVien.Tables[0].Rows[0]["Tong"].ToString());
            }
            else
            {
                numAffected = 0;
            }
            return numAffected;
        }
        public DataSet loadReportCountHocVienByWhere(string strNguon, string strNguoidung, string strTrangThai, int LevelID)
        {
            SqlParameter[] parameters = {   
                                            new SqlParameter("@strNguon", SqlDbType.VarChar, 300) ,
                                            new SqlParameter("@strNguoidung", SqlDbType.VarChar, 300) ,
                                            new SqlParameter("@strTrangThai", SqlDbType.VarChar, 300) ,
                                            new SqlParameter("@LevelID", SqlDbType.Int, 4) 
                                        };
            parameters[0].Value = strNguon;
            parameters[1].Value = strNguoidung;
            parameters[2].Value = strTrangThai;
            parameters[3].Value = LevelID;
            // create the parameter
            DataSet dsHocVien = RunProcedure("sp_HocVien_loadReportCountHocVienByWhere", parameters, "Tb_HocVien", connec);
            for (int i = 0; i < dsHocVien.Tables[0].Rows.Count; i++)
            {
                dsHocVien.Tables[0].Rows[i]["Seq"] = (i + 1).ToString();
            }
            return dsHocVien;
        }
        public DataSet loadTimDanhSachHocVien(int NguoiDungID, int LevelID, string HoTen, string MaHocVien
                            , string DienThoai, string Email, int TrangThaiGoiID, int TruongID)
        {
            SqlParameter[] parameters = {   new SqlParameter("@NguoiDungID", SqlDbType.Int, 4),
                                            new SqlParameter("@LevelID", SqlDbType.Int, 4) ,
                                            new SqlParameter("@HoTen", SqlDbType.NVarChar, 128) ,
                                            new SqlParameter("@MaHocVien", SqlDbType.VarChar, 20) ,
                                            new SqlParameter("@DienThoai", SqlDbType.VarChar, 64) ,
                                            new SqlParameter("@Email", SqlDbType.NVarChar, 64) ,
                                            new SqlParameter("@TrangThaiGoiID", SqlDbType.Int, 4) ,
                                            new SqlParameter("@TruongID", SqlDbType.Int, 4) 
                                        };
            parameters[0].Value = NguoiDungID;
            parameters[1].Value = LevelID;
            parameters[2].Value = HoTen.Trim();
            parameters[3].Value = MaHocVien.Trim();
            parameters[4].Value = DienThoai.Trim();
            parameters[5].Value = Email.Trim();
            parameters[6].Value = TrangThaiGoiID;
            parameters[7].Value = TruongID;
            // create the parameter
            DataSet dsHocVien = RunProcedure("sp_HocVien_loadTimDanhSachHocVien", parameters, "Tb_HocVien", connec);
            for (int i = 0; i < dsHocVien.Tables[0].Rows.Count; i++)
            {
                dsHocVien.Tables[0].Rows[i]["Seq"] = (i + 1).ToString();
            }
            return dsHocVien;
        }

        /*
         Created by LinhDH 
         * created date: 23/08/2012
         */
        public DataSet searchTimDanhSachHocVien(int NguoiDungID, int LevelID, string HoTen, string MaHocVien
                           , string DienThoai, string Email, int TrangThaiGoiID, int TruongID
                           , string ip_str_nhap_tu_ngay, string ip_str_nhap_tu_thang, string ip_str_nhap_tu_nam
                           , string ip_str_nhap_den_ngay, string ip_str_nhap_den_thang, string ip_str_nhap_den_nam
                           , string ip_str_ngay, string ip_str_thang, string ip_str_nam
                           , string ip_str_den_ngay, string ip_str_den_thang, string ip_str_den_nam)
        {
            SqlParameter[] parameters = {   new SqlParameter("@NguoiDungID", SqlDbType.Int, 4),
                                            new SqlParameter("@LevelID", SqlDbType.Int, 4) ,
                                            new SqlParameter("@HoTen", SqlDbType.NVarChar, 128) ,
                                            new SqlParameter("@MaHocVien", SqlDbType.VarChar, 20) ,
                                            new SqlParameter("@DienThoai", SqlDbType.VarChar, 64) ,
                                            new SqlParameter("@Email", SqlDbType.NVarChar, 64) ,
                                            new SqlParameter("@TrangThaiGoiID", SqlDbType.Int, 4) ,
                                            new SqlParameter("@TruongID", SqlDbType.Int, 4),
                                            new SqlParameter("@TuNgay", SqlDbType.VarChar, 4),
                                            new SqlParameter("@TuThang", SqlDbType.VarChar, 4), 
                                            new SqlParameter("@TuNam", SqlDbType.VarChar, 4),
                                            new SqlParameter("@DenNgay", SqlDbType.VarChar, 4),
                                            new SqlParameter("@DenThang", SqlDbType.VarChar, 4), 
                                            new SqlParameter("@DenNam", SqlDbType.VarChar, 4),
                                            new SqlParameter("@NhapTuNgay", SqlDbType.VarChar, 4),
                                            new SqlParameter("@NhapTuThang", SqlDbType.VarChar, 4), 
                                            new SqlParameter("@NhapTuNam", SqlDbType.VarChar, 4),
                                            new SqlParameter("@NhapDenNgay", SqlDbType.VarChar, 4),
                                            new SqlParameter("@NhapDenThang", SqlDbType.VarChar, 4), 
                                            new SqlParameter("@NhapDenNam", SqlDbType.VarChar, 4)
                                        };
            parameters[0].Value = NguoiDungID;
            parameters[1].Value = LevelID;
            parameters[2].Value = HoTen.Trim();
            parameters[3].Value = MaHocVien.Trim();
            parameters[4].Value = DienThoai.Trim();
            parameters[5].Value = Email.Trim();
            parameters[6].Value = TrangThaiGoiID;
            parameters[7].Value = TruongID;

            parameters[8].Value = ip_str_ngay;
            parameters[9].Value = ip_str_thang;
            parameters[10].Value = ip_str_nam;
            parameters[11].Value = ip_str_den_ngay;
            parameters[12].Value = ip_str_den_thang;
            parameters[13].Value = ip_str_den_nam;

            parameters[14].Value = ip_str_nhap_tu_ngay;
            parameters[15].Value = ip_str_nhap_tu_thang;
            parameters[16].Value = ip_str_nhap_tu_nam;
            parameters[17].Value = ip_str_nhap_den_ngay;
            parameters[18].Value = ip_str_nhap_den_thang;
            parameters[19].Value = ip_str_nhap_den_nam;
            // create the parameter
            DataSet dsHocVien = RunProcedure("sp_HocVien_searchTimDanhSachHocVien", parameters, "Tb_HocVien", connec);
            for (int i = 0; i < dsHocVien.Tables[0].Rows.Count; i++)
            {
                dsHocVien.Tables[0].Rows[i]["Seq"] = (i + 1).ToString();
            }
            return dsHocVien;
        }

        /*Create by: tinhth
         || Create date: 14h48-09-08-2011*/
        public DataSet loadTimDanhSachHocVienTVTS(string HoTen, string MaHocVien
                         , string DienThoai, string Email, string Category)
        {
            SqlParameter[] parameters = {   new SqlParameter("@HoTen", SqlDbType.NVarChar, 128) ,
                                            new SqlParameter("@MaHocVien", SqlDbType.VarChar, 20) ,
                                            new SqlParameter("@DienThoai", SqlDbType.VarChar, 64) ,
                                            new SqlParameter("@Email", SqlDbType.VarChar, 64) ,
                                            new SqlParameter("@Category", SqlDbType.NChar, 20)
                                        };
            parameters[0].Value = HoTen.Trim();
            parameters[1].Value = MaHocVien.Trim();
            parameters[2].Value = DienThoai.Trim();
            parameters[3].Value = Email.Trim();
            parameters[4].Value = Category.Trim();
            // create the parameter
            DataSet dsHocVien = RunProcedure("sp_HocVien_loadTimDanhSachHocVien_V2_Trung", parameters, "Tb_HocVien", connec);
            for (int i = 0; i < dsHocVien.Tables[0].Rows.Count; i++)
            {
                dsHocVien.Tables[0].Rows[i]["Seq"] = (i + 1).ToString();
            }
            return dsHocVien;
        }

        /*end tinhth*/
        /*Create by: tinhth
         || check so dien thoai trung khong?*/
        public string checkSoDienThoaiHocVien(string ip_str_so_dien_thoai)
        {
            int numAffected;
            // create the parameters
            SqlParameter[] parameters =  {	   new SqlParameter("@SoDienThoai", SqlDbType.VarChar, 20)
                                               , new SqlParameter("@ListUngVienTrung", SqlDbType.NVarChar, 500)
										 };
            // set the values
            parameters[1].Direction = ParameterDirection.Output;
            parameters[0].Value = ip_str_so_dien_thoai;
            // run the procedure
            RunProcedure("sp_HocVien_check_so_dien_thoai", parameters, out numAffected, connec);
            return parameters[1].Value.ToString();
        }

        /// <summary>
        /// Created by LinhDH 2012.05.14
        /// Check Email trùng ứng viên
        /// </summary>
        /// <param name="ip_str_so_dien_thoai"></param>
        /// <returns></returns>
        public string checkEmailHocVien(string ip_str_email)
        {
            int numAffected;
            // create the parameters
            SqlParameter[] parameters =  {	   new SqlParameter("@Email", SqlDbType.VarChar, 50)
                                              ,new SqlParameter("@ListUngVienTrung", SqlDbType.NVarChar, 500)
										 };
            // set the values
            parameters[1].Direction = ParameterDirection.Output;
            parameters[0].Value = ip_str_email;
            // run the procedure
            RunProcedure("sp_HocVien_check_email", parameters, out numAffected, connec);
            return parameters[1].Value.ToString();
        }

        /// <summary>
        /// Created by LinhDH 2012.07.19
        /// Check Email trùng ứng viên Update
        /// </summary>
        /// <param name="ip_str_so_dien_thoai"></param>
        /// <returns></returns>
        public string checkEmailHocVienUpdate(string ip_str_email, int ip_i_id_hoc_vien)
        {
            int numAffected;
            // create the parameters
            SqlParameter[] parameters =  {	   new SqlParameter("@Email", SqlDbType.VarChar, 50)
                                              ,new SqlParameter("@ListUngVienTrung", SqlDbType.NVarChar, 500)
                                              ,new SqlParameter("@IdUngvien", SqlDbType.Int,8)
										 };
            // set the values
            parameters[1].Direction = ParameterDirection.Output;
            parameters[0].Value = ip_str_email;
            parameters[2].Value = ip_i_id_hoc_vien;
            // run the procedure
            RunProcedure("sp_HocVien_check_email_Update", parameters, out numAffected, connec);
            return parameters[1].Value.ToString();
        }

        /*Create by: linhdh
      || check so dien thoai trung khong?*/
        public string checkSoDienThoaiHocVienUpdate(string ip_str_so_dien_thoai, int ip_i_id_hoc_vien)
        {
            int numAffected;
            // create the parameters
            SqlParameter[] parameters =  {	   new SqlParameter("@SoDienThoai", SqlDbType.VarChar, 20)
                                               , new SqlParameter("@ListUngVienTrung", SqlDbType.NVarChar, 500)
                                               ,new SqlParameter("@IdUngvien", SqlDbType.Int,8)
										 };
            // set the values
            parameters[1].Direction = ParameterDirection.Output;
            parameters[0].Value = ip_str_so_dien_thoai;
            parameters[2].Value = ip_i_id_hoc_vien;
            // run the procedure
            RunProcedure("sp_HocVien_check_so_dien_thoaiUpdate", parameters, out numAffected, connec);
            return parameters[1].Value.ToString();
        }
        /*end linhdh*/
        public DataSet loadTimDanhA(string HoTen
                           , string DienThoai)
        {
            SqlParameter[] parameters = {   new SqlParameter("@HoTen", SqlDbType.NVarChar, 128) ,
                                            new SqlParameter("@DienThoai", SqlDbType.VarChar, 64)
                                        };
            parameters[0].Value = HoTen.Trim();
            parameters[1].Value = DienThoai.Trim();
            // create the parameter
            DataSet dsHocVien = RunProcedure("sp_HocVien_loadTimDanhA", parameters, "Tb_HocVien", connec);
            for (int i = 0; i < dsHocVien.Tables[0].Rows.Count; i++)
            {
                dsHocVien.Tables[0].Rows[i]["Seq"] = (i + 1).ToString();
            }
            return dsHocVien;
        }
        public DataSet loadDanhSachHocVienTheoNguoiDung(int NguoiDungID, int LevelID)
        {
            SqlParameter[] parameters = {   new SqlParameter("@NguoiDungID", SqlDbType.Int, 4),
                                            new SqlParameter("@LevelID", SqlDbType.Int, 4) 
                                        };
            parameters[0].Value = NguoiDungID;
            parameters[1].Value = LevelID;

            // create the parameter
            DataSet dsHocVien = RunProcedure("sp_HocVien_loadDanhSachHocVienTheoNguoiDung", parameters, "Tb_HocVien", connec);
            for (int i = 0; i < dsHocVien.Tables[0].Rows.Count; i++)
            {
                dsHocVien.Tables[0].Rows[i]["Seq"] = (i + 1).ToString();
            }
            return dsHocVien;
        }
        public DataSet loadHocVien()
        {
            // create the parameter
            DataSet dsHocVien = RunProcedure("sp_HocVien_loadHocVien", "Tb_HocVien", connec);
            for (int i = 0; i < dsHocVien.Tables[0].Rows.Count; i++)
            {
                dsHocVien.Tables[0].Rows[i]["Seq"] = (i + 1).ToString();
            }
            return dsHocVien;
        }

        // return only the record with the specified ID!!!!!!!!!!!
        public DataRow loadHocVienID(int HocVienId)
        {
            // create the parameter
            SqlParameter[] parameters = { new SqlParameter("@ID", SqlDbType.Int, 4) };
            parameters[0].Value = HocVienId;

            using (DataSet HocViens = RunProcedure("sp_HocVien_loadHocVienID", parameters, "Tb_HocVien", connec))
            {
                return HocViens.Tables[0].Rows[0];
            }
        }
        // update the properties of the specified HocViens
        // Sua noi dung ban ghi hien hanh
        public int updateHocVienLevel(int HocVienID, int LevelID, int NguoiDungID, int TrangThaiGoiID, int TrangThaiID)
        {
            int numAffected;
            // create the parameters
            SqlParameter[] parameters = { 	    new SqlParameter("@ID", SqlDbType.Int ,4),
                                                new SqlParameter("@LevelID", SqlDbType.Int ,4),
                                                new SqlParameter("@NguoiDungID", SqlDbType.Int ,4),
                                                new SqlParameter("@TrangThaiGoiID", SqlDbType.Int ,4),
                                                new SqlParameter("@TrangThaiID", SqlDbType.Int ,4)
										};
            // set the values
            parameters[0].Value = HocVienID;
            parameters[1].Value = LevelID;
            parameters[2].Value = NguoiDungID;
            parameters[3].Value = TrangThaiGoiID;
            parameters[4].Value = TrangThaiID;
            // run the procedure
            RunProcedure("sp_HocVien_updateHocVienLevel", parameters, out numAffected, connec);

            return numAffected;
        }

        // update the properties of the specified HocViens
        // Sua noi dung ban ghi hien hanh
        public int updateHocVien(int HocVienID, string HoTen, string DienThoai, string DiaChiThu, bool GioiTinh,
                    DateTime NgaySinh, string Email, string DiLam, int NguoiDungID, string MaHocVien, int NguoiTimNguonID,
                    int NguonID, string GhiChuNguon, int TruongID)
        {
            int numAffected;
            // create the parameters
            SqlParameter[] parameters = { 	    new SqlParameter("@ID", SqlDbType.Int ,4),
                                                new SqlParameter("@HoTen", SqlDbType.NVarChar, 128),
                                                new SqlParameter("@DienThoai", SqlDbType.VarChar, 64),
                                                new SqlParameter("@DiaChiThu", SqlDbType.NVarChar, 256),
                                                new SqlParameter("@GioiTinh", SqlDbType.Bit ),
                                                new SqlParameter("@NgaySinh", SqlDbType.DateTime),
                                                new SqlParameter("@Email", SqlDbType.NVarChar, 64),
                                                new SqlParameter("@DiLam", SqlDbType.NVarChar, 1024),
                                                new SqlParameter("@NguoiDungID", SqlDbType.Int ,4),
                                                new SqlParameter("@MaHocVien", SqlDbType.VarChar, 20),
                                                new SqlParameter("@NguoiTimNguonID", SqlDbType.Int ,4),
                                                new SqlParameter("@NguonID", SqlDbType.Int ,4),
                                                new SqlParameter("@GhiChuNguon", SqlDbType.NVarChar, 256)  ,
                                                new SqlParameter("@TruongID", SqlDbType.Int ,4)
										};
            // set the values
            parameters[0].Value = HocVienID;
            parameters[1].Value = HoTen.Trim();
            parameters[2].Value = DienThoai.Trim();
            parameters[3].Value = DiaChiThu.Trim();
            parameters[4].Value = GioiTinh;
            parameters[5].Value = NgaySinh;
            parameters[6].Value = Email.Trim();
            parameters[7].Value = DiLam.Trim();
            parameters[8].Value = NguoiDungID;
            parameters[9].Value = MaHocVien.Trim();
            parameters[10].Value = NguoiTimNguonID;
            parameters[11].Value = NguonID;
            parameters[12].Value = GhiChuNguon.Trim();
            parameters[13].Value = TruongID;
            // run the procedure
            RunProcedure("sp_HocVien_updateHocVien", parameters, out numAffected, connec);

            return numAffected;
        }

        public DataTable load_all_hoc_vien(int NguoidungID)
        {
            SqlParameter[] parameters =  {	   new SqlParameter("@NguoidungID", SqlDbType.VarChar, 20)
										 };
            parameters[0].Value = NguoidungID;
            DataSet v_ds_data = RunProcedure("sp_HocVien_loadAllDanhSachHocVien", parameters, "Tb_HocVien", connec);
            return v_ds_data.Tables[0];
        }
        // add a new question
        // Them ban ghi hien hanh
        public int insertHocVien(string HoTen, string DienThoai, string DiaChiThu, bool GioiTinh,
                    DateTime NgaySinh, string Email, string DiLam, int NguoiDungID, string MaHocVien, int NguoiTimNguonID,
                    int NguonID, string GhiChuNguon, int LevelID, int TruongID, string category, int UserID, string DienThoai2, string Email2)
        {
            int numAffected;
            // create the parameters
            SqlParameter[] parameters =  {	    new SqlParameter("@ID", SqlDbType.Int ,4),
                                                new SqlParameter("@HoTen", SqlDbType.NVarChar, 128),
                                                new SqlParameter("@DienThoai", SqlDbType.VarChar, 64),
                                                new SqlParameter("@DiaChiThu", SqlDbType.NVarChar, 256),
                                                new SqlParameter("@GioiTinh", SqlDbType.Bit ),
                                                new SqlParameter("@NgaySinh", SqlDbType.DateTime),
                                                new SqlParameter("@Email", SqlDbType.NVarChar, 64),
                                                new SqlParameter("@DiLam", SqlDbType.NVarChar, 1024),
                                                new SqlParameter("@NguoiDungID", SqlDbType.Int ,4),
                                                new SqlParameter("@MaHocVien", SqlDbType.VarChar, 20),
                                                new SqlParameter("@NguoiTimNguonID", SqlDbType.Int ,4),
                                                new SqlParameter("@NguonID", SqlDbType.Int ,4),
                                                new SqlParameter("@GhiChuNguon", SqlDbType.NVarChar, 256),
											    new SqlParameter("@LevelID", SqlDbType.Int ,4),
                                                new SqlParameter("@TruongID", SqlDbType.Int ,4),
                                                new SqlParameter("@Category", SqlDbType.VarChar ,24),
                                                new SqlParameter("@UserID", SqlDbType.Int ,4),
                                                new SqlParameter("@DienThoai2", SqlDbType.VarChar, 64),
                                                new SqlParameter("@Email2", SqlDbType.NVarChar, 64)
										 };
            // set the values
            parameters[1].Value = HoTen.Trim();
            parameters[2].Value = DienThoai.Trim();
            parameters[3].Value = DiaChiThu.Trim();
            parameters[4].Value = GioiTinh;
            parameters[5].Value = NgaySinh;
            parameters[6].Value = Email.Trim();
            parameters[7].Value = DiLam.Trim();
            parameters[8].Value = NguoiDungID;
            parameters[9].Value = MaHocVien.Trim();
            parameters[10].Value = NguoiTimNguonID;
            parameters[11].Value = NguonID;
            parameters[12].Value = GhiChuNguon.Trim();
            parameters[13].Value = LevelID;
            parameters[14].Value = TruongID;
            parameters[15].Value = category;
            parameters[16].Value = UserID;
            parameters[17].Value = DienThoai2.Trim();
            parameters[18].Value = Email2.Trim();
            parameters[0].Direction = ParameterDirection.Output;

            // run the procedure
            RunProcedure("sp_HocVien_insertHocVienTrung", parameters, out numAffected, connec);

            return (int)parameters[0].Value;
        }
        // add a new question
        // Them ban ghi hien hanh
        public int importHocVien(string HoTen, string DienThoai, string DiaChiThu,
                    string Email, string DiLam, int NguoiDungID, string MaHocVien, int NguoiTimNguonID,
                    string GhiChuNguon, int LevelID)
        {
            int numAffected;
            // create the parameters
            SqlParameter[] parameters =  {	    new SqlParameter("@ID", SqlDbType.Int ,4),
                                                new SqlParameter("@HoTen", SqlDbType.NVarChar, 128),
                                                new SqlParameter("@DienThoai", SqlDbType.VarChar, 64),
                                                new SqlParameter("@DiaChiThu", SqlDbType.NVarChar, 256),
                                                new SqlParameter("@Email", SqlDbType.NVarChar, 64),
                                                new SqlParameter("@DiLam", SqlDbType.NVarChar, 1024),
                                                new SqlParameter("@NguoiDungID", SqlDbType.Int ,4),
                                                new SqlParameter("@MaHocVien", SqlDbType.VarChar, 20),
                                                new SqlParameter("@NguoiTimNguonID", SqlDbType.Int ,4),
                                                new SqlParameter("@GhiChuNguon", SqlDbType.NVarChar, 256),
											    new SqlParameter("@LevelID", SqlDbType.Int ,4)
										 };
            // set the values
            parameters[1].Value = HoTen.Trim();
            parameters[2].Value = DienThoai.Trim();
            parameters[3].Value = DiaChiThu.Trim();
            parameters[4].Value = Email.Trim();
            parameters[5].Value = DiLam.Trim();
            parameters[6].Value = NguoiDungID;
            parameters[7].Value = MaHocVien.Trim();
            parameters[8].Value = NguoiTimNguonID;
            parameters[9].Value = GhiChuNguon.Trim();
            parameters[10].Value = LevelID;
            parameters[0].Direction = ParameterDirection.Output;

            // run the procedure
            RunProcedure("sp_HocVien_importHocVien", parameters, out numAffected, connec);

            return (int)parameters[0].Value;
        }
        // Xoa Role
        public int removeHocVien(int HocVienID)
        {
            int numAffected;
            // create the parameters
            SqlParameter[] parameters = { new SqlParameter("@ID", SqlDbType.Int) };
            parameters[0].Value = HocVienID;
            // run the procedure
            try
            {
                RunProcedure("sp_HocVien_removeHocViens", parameters, out numAffected, connec);
            }
            catch
            {
                numAffected = 0;
            }
            return numAffected;
        }
    }
}